package com.tuanzi.loan.business.repository.family.impl;

import static com.tuanzi.loan.generated.jooq.loan.tables.LoanPrepaymentsConsumptionRecord.LOAN_PREPAYMENTS_CONSUMPTION_RECORD;
import static com.tuanzi.loan.generated.jooq.loan.tables.LoanPrepaymentsProduct.LOAN_PREPAYMENTS_PRODUCT;
import static com.tuanzi.loan.generated.jooq.loan.tables.LoanPrepaymentsRechargeRecord.LOAN_PREPAYMENTS_RECHARGE_RECORD;
import static com.tuanzi.loan.generated.jooq.loan.tables.LoanProductInfo.LOAN_PRODUCT_INFO;

import java.math.BigDecimal;
import java.sql.Date;
import java.sql.Timestamp;
import java.util.List;
import java.util.Objects;

import org.jooq.*;
import org.jooq.impl.DSL;
import org.jooq.impl.TableImpl;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Repository;

import com.tuanzi.loan.business.repository.family.FirstPartyPrepaymentRepository;
import com.tuanzi.loan.core.repository.JOOQBaseRepositoryPlus;
import com.tuanzi.loan.vo.request.family.BusinessPrepaymentSearchRequest;
import com.tuanzi.loan.vo.request.family.PartnerBalanceSearchRequest;
import com.tuanzi.loan.vo.response.family.BusinessPrepaymentSearchResponse;
import com.tuanzi.loan.vo.response.family.PartnerBalanceSearchResponse;

/**
 * 甲方预付费充值
 * @author JinRuiyang
 */
@Repository
public class FirstPartyPrepaymentRepositoryImpl extends JOOQBaseRepositoryPlus implements FirstPartyPrepaymentRepository {



    /**
     * 创建商务充值
     *
     * @param businessPrepayment 模板ID，合作方名，点击单价, 充值金额, 操作人
     * @return 结果
     */
    @Override
    public Integer create(BusinessPrepaymentSearchRequest businessPrepayment) {
        Integer insertResult = dslContext.insertInto(LOAN_PREPAYMENTS_PRODUCT,
                LOAN_PREPAYMENTS_PRODUCT.MODELID,
                LOAN_PREPAYMENTS_PRODUCT.PRODUCT_NAME,
                LOAN_PREPAYMENTS_PRODUCT.AMOUT_PER_CLICK,
                LOAN_PREPAYMENTS_PRODUCT.BANLANCE,
                LOAN_PREPAYMENTS_PRODUCT.OPERATOR_NAME)
                .values(businessPrepayment.getModelId(),
                        businessPrepayment.getPartnerName(),
                        businessPrepayment.getAmountPerClick(),
                        0d,
                        businessPrepayment.getOperatorName())
                .execute();
        if (insertResult == 1 && businessPrepayment.getRechargeBalance() > 0d)
            insertResult = this.recharge(businessPrepayment);
        return insertResult;
    }

    /**
     * 更新商务充值
     *
     * @param businessPrepayment 模板ID，合作方名，操作人，点击单价
     * @return 结果
     */
    @Override
    public Integer update(BusinessPrepaymentSearchRequest businessPrepayment) {
        return dslContext.update(LOAN_PREPAYMENTS_PRODUCT)
                .set(LOAN_PREPAYMENTS_PRODUCT.PRODUCT_NAME,businessPrepayment.getPartnerName())
                .set(LOAN_PREPAYMENTS_PRODUCT.AMOUT_PER_CLICK,businessPrepayment.getAmountPerClick())
                .set(LOAN_PREPAYMENTS_PRODUCT.OPERATOR_NAME,businessPrepayment.getOperatorName())
                .where(LOAN_PREPAYMENTS_PRODUCT.MODELID.eq(businessPrepayment.getModelId()))
                .and(LOAN_PREPAYMENTS_PRODUCT.STATUS.eq(PrepaymentProductStatus.ENABLE.getValue()))
                .execute();
    }

    /**
     * 充值,添加充值记录，更新商务充值的操作人信息
     * 充值金额可为负数,但余额不可为负数
     *
     * @param businessPrepayment 模板ID，合作方名, 充值金额, 操作人
     * @return 结果
     */
    @Override
    public Integer recharge(BusinessPrepaymentSearchRequest businessPrepayment) {
        // 如果充值金额为负数检查余额是否足够扣除
        if (businessPrepayment.getRechargeBalance() < 0){
            Double maxNegativeRechargeAmount = this.getPrepaymentBalance(businessPrepayment.getModelId());
            if (maxNegativeRechargeAmount < -businessPrepayment.getRechargeBalance())
                throw new IllegalArgumentException("negative recharge is bigger than balance");
        }

        // 充值金额到商务充值
        Integer updateResult = dslContext.update(LOAN_PREPAYMENTS_PRODUCT)
                .set(LOAN_PREPAYMENTS_PRODUCT.BANLANCE,DSL.field("`banlance` + ?",Double.class,businessPrepayment.getRechargeBalance()))
                .set(LOAN_PREPAYMENTS_PRODUCT.OPERATOR_NAME,businessPrepayment.getOperatorName())
                .where(LOAN_PREPAYMENTS_PRODUCT.MODELID.eq(businessPrepayment.getModelId()))
                .and(LOAN_PREPAYMENTS_PRODUCT.STATUS.eq(PrepaymentProductStatus.ENABLE.getValue()))
                .execute();
        if (updateResult != 1){
            throw new IllegalArgumentException("target business prepayment is invalid");
        }
        // 添加充值记录
        java.util.Date time = new java.util.Date();
        updateResult = dslContext.insertInto(LOAN_PREPAYMENTS_RECHARGE_RECORD,
                LOAN_PREPAYMENTS_RECHARGE_RECORD.MODELID,
                LOAN_PREPAYMENTS_RECHARGE_RECORD.PRODUCT_NAME,
                LOAN_PREPAYMENTS_RECHARGE_RECORD.RECHARGE_VALUE,
                LOAN_PREPAYMENTS_RECHARGE_RECORD.CREATEDATE,
                LOAN_PREPAYMENTS_RECHARGE_RECORD.CREATETIME,
                LOAN_PREPAYMENTS_RECHARGE_RECORD.OPERATOR_NAME)
                .values(businessPrepayment.getModelId(),
                        businessPrepayment.getPartnerName(),
                        businessPrepayment.getRechargeBalance(),
                        new Date(time.getTime()),
                        new Timestamp(time.getTime()),
                        businessPrepayment.getOperatorName())
                .execute();
        return updateResult;
    }

    /**
     * 获取所有商务充值的模板ID，用于查询数据权限使用
     *
     * @return 模板ID列表
     */
    @Override
    public List<Long> getAllModelId() {
        return dslContext
                .select(LOAN_PREPAYMENTS_PRODUCT.MODELID)
                .from(LOAN_PREPAYMENTS_PRODUCT)
                .fetch(LOAN_PREPAYMENTS_PRODUCT.MODELID);
    }

    /**
     * 获取商务充值余额
     * @param modelId 模板ID
     * @return 充值金额
     */
    public Double getPrepaymentBalance(Long modelId){
        Double balance = dslContext
                .select(LOAN_PREPAYMENTS_PRODUCT.BANLANCE)
                .from(LOAN_PREPAYMENTS_PRODUCT)
                .where(LOAN_PREPAYMENTS_PRODUCT.MODELID.eq(modelId))
                .fetchOne(LOAN_PREPAYMENTS_PRODUCT.BANLANCE);
        if (Objects.isNull(balance))
            throw new NullPointerException("modelId is invalid");
        return balance;
    }

    /**
     * 更新活动状态
     * @param modelId 模板ID
     * @param status 活动状态 1：上线 / 0：下线
     * @return 更新结果
     */
    public Integer updateActivityStatus(Long modelId,Integer status){
        return dslContext.update(LOAN_PRODUCT_INFO)
                .set(LOAN_PRODUCT_INFO.STATUS, status)
                .where(LOAN_PRODUCT_INFO.MODELID.eq(modelId))
                .execute();
    }

    /**
     * 删除商务充值
     * 将状态标记为不可用
     *
     * @param modelId 模板ID
     * @param status 状态码
     * @return 结果
     */
    @Override
    public Integer changeStatus(Long modelId, PrepaymentProductStatus status ) {
        return dslContext.update(LOAN_PREPAYMENTS_PRODUCT)
                .set(LOAN_PREPAYMENTS_PRODUCT.STATUS,status.getValue())
                .where(LOAN_PREPAYMENTS_PRODUCT.MODELID.eq(modelId))
                .execute();
    }

    /**
     * 商务充值查询
     *
     * @param searchRequest 模板ID，合作方名
     * @param pageable      分页信息
     * @return 响应列表
     */
    @Override
    public Page<BusinessPrepaymentSearchResponse> listBusinessPrepayment(BusinessPrepaymentSearchRequest searchRequest, Pageable pageable) {
        fields(LOAN_PREPAYMENTS_PRODUCT.fields());
        contains(LOAN_PREPAYMENTS_PRODUCT.PRODUCT_NAME,searchRequest.getPartnerNameInput());
        eq(LOAN_PREPAYMENTS_PRODUCT.MODELID,searchRequest.getModelIdInput());
        return search(pageable, new BusinessPrepaymentSearchResponseMapper());
    }

    /**
     * 合作方余额查询
     *
     * @param searchRequest 开始时间，结束时间，模板ID，合作方名
     * @param pageable      分页信息
     * @return 响应列表
     */
    @Override
    public Page<PartnerBalanceSearchResponse> listPartnerBalance(PartnerBalanceSearchRequest searchRequest, Pageable pageable) {

        SelectConditionStep<Record2<Long,Date>> cDateUnionSelect = dslContext
                .selectDistinct(LOAN_PREPAYMENTS_CONSUMPTION_RECORD.MODEL_ID.as("modelId"),
                        LOAN_PREPAYMENTS_CONSUMPTION_RECORD.CREATEDATE.as("createDate"))
                .from(LOAN_PREPAYMENTS_CONSUMPTION_RECORD)
                .where(DSL.trueCondition());

        SelectConditionStep<Record2<Long,Date>> rDateUnionSelect = dslContext
                .selectDistinct(LOAN_PREPAYMENTS_RECHARGE_RECORD.MODELID,
                        LOAN_PREPAYMENTS_RECHARGE_RECORD.CREATEDATE)
                .from(LOAN_PREPAYMENTS_RECHARGE_RECORD)
                .where(DSL.trueCondition());

        // 子查询 c 除了当天总充值外的所以字段
        SelectConditionStep<Record4<Long,Date,Integer,BigDecimal>> selectConditionStepc = dslContext
                .select(LOAN_PREPAYMENTS_CONSUMPTION_RECORD.MODEL_ID,
                        LOAN_PREPAYMENTS_CONSUMPTION_RECORD.CREATEDATE,
                        DSL.count(LOAN_PREPAYMENTS_CONSUMPTION_RECORD.ID).as("clickCount"),
                        DSL.sum(LOAN_PREPAYMENTS_CONSUMPTION_RECORD.COST_PRICE).as("consumerAmount"))
                .from(LOAN_PREPAYMENTS_CONSUMPTION_RECORD)
                .where(DSL.trueCondition());

        // 子查询 r 当天总充值及连表数据
        SelectConditionStep<Record3<Long,Date,BigDecimal>> selectConditionStepr = dslContext
                .select(LOAN_PREPAYMENTS_RECHARGE_RECORD.MODELID,
                        LOAN_PREPAYMENTS_RECHARGE_RECORD.CREATEDATE,
                        DSL.sum(LOAN_PREPAYMENTS_RECHARGE_RECORD.RECHARGE_VALUE).as("sumRecharge"))
                .from(LOAN_PREPAYMENTS_RECHARGE_RECORD)
                .where(DSL.trueCondition());

        if (searchRequest.getStartTime() != null){
            Date date = new Date(searchRequest.getStartTime().getTime());
            cDateUnionSelect = cDateUnionSelect.and(LOAN_PREPAYMENTS_CONSUMPTION_RECORD.CREATEDATE.ge(date));
            rDateUnionSelect = rDateUnionSelect.and(LOAN_PREPAYMENTS_RECHARGE_RECORD.CREATEDATE.ge(date));
            selectConditionStepc = selectConditionStepc.and(LOAN_PREPAYMENTS_CONSUMPTION_RECORD.CREATEDATE.ge(date));
            selectConditionStepr = selectConditionStepr.and(LOAN_PREPAYMENTS_RECHARGE_RECORD.CREATEDATE.ge(date));
        }
        if (searchRequest.getEndTime() != null){
            Date date = new Date(searchRequest.getEndTime().getTime());
            cDateUnionSelect = cDateUnionSelect.and(LOAN_PREPAYMENTS_CONSUMPTION_RECORD.CREATEDATE.le(date));
            rDateUnionSelect = rDateUnionSelect.and(LOAN_PREPAYMENTS_RECHARGE_RECORD.CREATEDATE.le(date));
            selectConditionStepc = selectConditionStepc.and(LOAN_PREPAYMENTS_CONSUMPTION_RECORD.CREATEDATE.le(date));
            selectConditionStepr = selectConditionStepr.and(LOAN_PREPAYMENTS_RECHARGE_RECORD.CREATEDATE.le(date));
        }
        if (searchRequest.getModelId() != null){
            cDateUnionSelect = cDateUnionSelect.and(LOAN_PREPAYMENTS_CONSUMPTION_RECORD.MODEL_ID.eq(searchRequest.getModelId()));
            rDateUnionSelect = rDateUnionSelect.and(LOAN_PREPAYMENTS_RECHARGE_RECORD.MODELID.eq(searchRequest.getModelId()));
            selectConditionStepc = selectConditionStepc.and(LOAN_PREPAYMENTS_CONSUMPTION_RECORD.MODEL_ID.eq(searchRequest.getModelId()));
            selectConditionStepr = selectConditionStepr.and(LOAN_PREPAYMENTS_RECHARGE_RECORD.MODELID.eq(searchRequest.getModelId()));
        }

        Table<Record4<Long,Date,Integer,BigDecimal>> tablec = selectConditionStepc
                .groupBy(LOAN_PREPAYMENTS_CONSUMPTION_RECORD.CREATEDATE,LOAN_PREPAYMENTS_CONSUMPTION_RECORD.MODEL_ID)
                .asTable("c");

        Table<Record3<Long,Date,BigDecimal>> tabler = selectConditionStepr
                .groupBy(LOAN_PREPAYMENTS_RECHARGE_RECORD.CREATEDATE,LOAN_PREPAYMENTS_RECHARGE_RECORD.MODELID)
                .asTable("r");

        // 首先构建日期表
        Table<Record2<Long,Date>> tableDate = cDateUnionSelect
                .union(rDateUnionSelect)
                .asTable("date");

        SelectOnConditionStep selectOnConditionStepFinal = dslContext.select(
                        LOAN_PREPAYMENTS_PRODUCT.BANLANCE,
                        LOAN_PREPAYMENTS_PRODUCT.PRODUCT_NAME,
                        tableDate.field("modelId"),
                        tableDate.field("createDate"),
                        tablec.field("clickCount"),
                        tablec.field("consumerAmount"),
                        tabler.field("sumRecharge"))
                .from(tableDate)
                .leftJoin(tablec)
                .on(tablec.field(LOAN_PREPAYMENTS_CONSUMPTION_RECORD.MODEL_ID).eq(tableDate.field("modelId",Long.class)))
                .and(tablec.field(LOAN_PREPAYMENTS_CONSUMPTION_RECORD.CREATEDATE).eq(tableDate.field("createDate",Date.class)))
                .leftJoin(tabler)
                .on(tabler.field(LOAN_PREPAYMENTS_RECHARGE_RECORD.MODELID).eq(tableDate.field("modelId",Long.class)))
                .and(tabler.field(LOAN_PREPAYMENTS_RECHARGE_RECORD.CREATEDATE).eq(tableDate.field("createDate",Date.class)))
                .join(LOAN_PREPAYMENTS_PRODUCT)
                .on(LOAN_PREPAYMENTS_PRODUCT.MODELID.eq(tableDate.field("modelId", Long.class)));
                //.and(LOAN_PREPAYMENTS_PRODUCT.STATUS.eq(PrepaymentProductStatus.ENABLE.getValue()));

        if (searchRequest.getPartnerName() != null){
            selectOnConditionStepFinal = selectOnConditionStepFinal.and(LOAN_PREPAYMENTS_PRODUCT.PRODUCT_NAME.contains(searchRequest.getPartnerName()));
        }
        SelectQuery selectQueryFinal = selectOnConditionStepFinal
                .orderBy(tableDate.field("createDate").desc())
                .getQuery();
        return search(selectQueryFinal, pageable, new PartnerBalanceSearchResponseMapper());
    }

    @Override
    protected TableImpl getTable() {
        return LOAN_PREPAYMENTS_PRODUCT;
    }

    private final class BusinessPrepaymentSearchResponseMapper implements RecordMapper<Record, BusinessPrepaymentSearchResponse> {
        @Override
        public BusinessPrepaymentSearchResponse map(Record record) {
            BusinessPrepaymentSearchResponse response = new BusinessPrepaymentSearchResponse();
            response.setId(record.get(LOAN_PREPAYMENTS_PRODUCT.MODELID));
            response.setModelId(record.get(LOAN_PREPAYMENTS_PRODUCT.MODELID));
            response.setPartnerName(record.get(LOAN_PREPAYMENTS_PRODUCT.PRODUCT_NAME));
            response.setAmountPerClick(record.get(LOAN_PREPAYMENTS_PRODUCT.AMOUT_PER_CLICK));
            response.setBalance(record.get(LOAN_PREPAYMENTS_PRODUCT.BANLANCE));
            response.setOperatorName(record.get(LOAN_PREPAYMENTS_PRODUCT.OPERATOR_NAME));
            response.setStatus(record.get(LOAN_PREPAYMENTS_PRODUCT.STATUS,Integer.class));
            return response;
        }
    }

    private final class PartnerBalanceSearchResponseMapper implements RecordMapper<Record, PartnerBalanceSearchResponse> {
        @Override
        public PartnerBalanceSearchResponse map(Record record) {
            PartnerBalanceSearchResponse response = new PartnerBalanceSearchResponse();
            response.setModelId(record.get("modelId",Long.class));
            response.setPartnerName(record.get(LOAN_PREPAYMENTS_PRODUCT.PRODUCT_NAME));
            response.setCreateDate(record.get("createDate",String.class));
            response.setBalance(record.get(LOAN_PREPAYMENTS_PRODUCT.BANLANCE));

            Long clickCount = record.get("clickCount",Long.class);
            response.setClickCount(Objects.isNull(clickCount) ? 0 : clickCount);

            Double consumeAmount = record.get("consumerAmount",Double.class);
            response.setConsumeAmount(Objects.isNull(consumeAmount) ? 0 : consumeAmount);

            Double sumRecharge = record.get("sumRecharge",Double.class);
            response.setRechargeBalance(Objects.isNull(sumRecharge) ? 0D : sumRecharge);
            return response;
        }
    }
}
